const institutional = require('./institutional'); // 处理权限字段

function disposeparams(params) {
  /*
    senderRegionalId  //寄件大区
    senderAgentCode  //寄件代理区
    senderDistributeCode //寄件转运中心
    senderNetworkCode   //寄件网点
    senderProvinceId  //寄件省
    senderCityId   //寄件市
    senderAreaId   //寄件区

    receiverRegionalId  //目的大区
    receiverAgentCode  //目的代理区
    receiverDistributeCode //目的转运中心
    receiverNetworkCode   //目的网点
    receiverProvinceId  //目的省
    receiverCityId   //目的市
    receiverAreaId   //目的区

    island             //是否桃花岛
    sign_type          //是否签收  -时效签收0  -实际签收1
    current  //页码
    size  //条数
    type  //时间类型1,2
    sign_type //签收类型 -时效签收（is_pres_sign）0  -实际签收( is_real_sign )1
    districtprescription  //区县时效0-10
  **/
  params.type = (params.type == "" || params.type == undefined) ? "" : `AND type =  '${params.type}' ` //时间类型1，2
  //是否签收  -时效签收（is_pres_sign）0  -实际签收( is_real_sign )1
  // params.sign_type_sql = (params.sign_type == 0) ? `AND is_pres_sign =  1 ` : `AND is_real_sign =  1 ` //是否签收
  params.sign_type_sql = (params.sign_type == 0) ? `AND (is_pres_sign =  1 or  is_pres_sign=0)` : `AND (is_real_sign =  1 or is_real_sign=0)` //是否签收
  params.island = (params.island === "" || params.island == undefined) ? "" : `AND island =  ${params.island}` //是否桃花岛
  params.districtprescription = (params.districtprescription.length > 0) ? `AND districtprescription in  (${params.districtprescription})` : ""

  params.senderRegionalId = (params.senderRegionalId == "" || params.senderRegionalId == undefined) ? "" : `AND start_regional_id =  '${params.senderRegionalId}'` //寄件大区
  params.senderAgentCode = (params.senderAgentCode == "" || params.senderAgentCode == undefined) ? "" : `AND start_subordinate_agent_code =  '${params.senderAgentCode}'` //寄件代理区
  params.senderDistributeCode = (params.senderDistributeCode == "" || params.senderDistributeCode == undefined) ? "" : `AND start_center_code =  '${params.senderDistributeCode}'` //寄件转运中心
  params.senderNetworkCode = (params.senderNetworkCode == "" || params.senderNetworkCode == undefined) ? "" : `AND start_pick_network_code =  '${params.senderNetworkCode}'` //寄件网点
  params.senderProvinceId = (params.senderProvinceId == "" || params.senderProvinceId == undefined) ? "" : `AND start_provider_id =  '${params.senderProvinceId}'` //寄件省
  params.senderCityId = (params.senderCityId == "" || params.senderCityId == undefined) ? "" : `AND start_city_id =  '${params.senderCityId}'` //寄件市
  params.senderAreaId = (params.senderAreaId == "" || params.senderAreaId == undefined) ? "" : `AND start_area_id =  '${params.senderAreaId}'` //寄件区

  params.receiverRegionalId = (params.receiverRegionalId == "" || params.receiverRegionalId == undefined) ? "" : `AND sign_regional_id =  '${params.receiverRegionalId}'` //目的大区
  params.receiverAgentCode = (params.receiverAgentCode == "" || params.receiverAgentCode == undefined) ? "" : `AND sign_subordinate_agent_code =  '${params.receiverAgentCode}'` //目的代理区
  params.receiverDistributeCode = (params.receiverDistributeCode == "" || params.receiverDistributeCode == undefined) ? "" : `AND aim_center_code =  '${params.receiverDistributeCode}'` //目的转运中心
  params.receiverNetworkCode = (params.receiverNetworkCode == "" || params.receiverNetworkCode == undefined) ? "" : `AND sign_network_code =  '${params.receiverNetworkCode}'` //目的网点
  params.receiverProvinceId = (params.receiverProvinceId == "" || params.receiverProvinceId == undefined) ? "" : `AND sign_provider_id =  '${params.receiverProvinceId}'` //目的省
  params.receiverCityId = (params.receiverCityId == "" || params.receiverCityId == undefined) ? "" : `AND sign_city_id =  '${params.receiverCityId}'` //目的市
  params.receiverAreaId = (params.receiverAreaId == "" || params.receiverAreaId == undefined) ? "" : `AND sign_area_id =  '${params.receiverAreaId}'` //目的区
  return params
}

function CFdimensionalityType(parms) {
  /*
  WdType:查询维度
  isPurpose:始发或者目的 1 进港  2出港(sign是进港,start是出港)
  **/
  let CFconcretely = "", CFconcretelyName = "--"
  switch (parms.WdType) {
    case 1:
      CFconcretely = (parms.isPurpose === 2) ? "start_regional_id" : "sign_regional_id" //大区
      CFconcretelyName = (parms.isPurpose === 2) ? "start_regional_desc" : "sign_regional_desc" //大区
      break;
    case 2:
      CFconcretely = (parms.isPurpose === 2) ? "start_subordinate_agent_code" : "sign_subordinate_agent_code" //代理区
      CFconcretelyName = (parms.isPurpose === 2) ? "start_subordinate_agent_name" : "sign_subordinate_agent_name" //代理区
      break;
    case 3:
      CFconcretely = (parms.isPurpose === 2) ? "start_center_code" : "aim_center_code" //转运中心
      CFconcretelyName = (parms.isPurpose === 2) ? "start_center_name" : "aim_center_name" //转运中心
      break;
    case 4:
      CFconcretely = (parms.isPurpose === 2) ? "start_pick_network_code" : "sign_network_code" //转运网点
      CFconcretelyName = (parms.isPurpose === 2) ? "start_pick_network_name" : "sign_network_name" //转运网点
      break;
    case 5:
      CFconcretely = (parms.isPurpose === 2) ? "start_provider_id" : "sign_provider_id" //省
      CFconcretelyName = (parms.isPurpose === 2) ? "start_provider_desc" : "sign_provider_desc" //省
      break;
    case 6:
      CFconcretely = (parms.isPurpose === 2) ? "start_city_id" : "sign_city_id" //市
      CFconcretelyName = (parms.isPurpose === 2) ? "start_city_desc" : "sign_city_desc" //市
      break;
    case 7:
      CFconcretely = (parms.isPurpose === 2) ? "start_area_id" : "sign_area_id" //区
      CFconcretelyName = (parms.isPurpose === 2) ? "start_area_desc" : "sign_area_desc" //区
      break;
  }
  return {
    CFconcretely: CFconcretely,
    CFconcretelyName: CFconcretelyName
  }

}

// 达成率汇总
module.exports = {
  // 全网-全网
  AchievingRateQuanwang_sql: (params) => {
    /* 
    senderRegionalId:寄件大区
    receiverRegionalId:签收大区 
    island:是否桃花岛
    sign_type:签收类型
    districtprescription:区县时效districtprescription(0-10)
    current:页码
    size:条数 
    startTime:开始时间
    endTime:结束时间
    **/
    const param = disposeparams(params)
    const sql = `
      SELECT
      count(1)
      FROM
          (
              SELECT
                date_time
              FROM
                jms_dws.dws_network_reach_rate
              WHERE
              date_time >= '${param.startTime}'
              AND date_time <= '${param.endTime}' -- 时间
              ${param.senderRegionalId}
              ${param.receiverRegionalId}
       
              ${param.type}
              ${param.sign_type_sql}
              ${param.island}
              ${param.districtprescription}
              group by date_time,--日期
              districtprescription --区县时效
          ) t;
       select date_time,--日期
          '全网' as start_desc, --始发区域
          '全网' as aim_desc,  --目的区域
          districtprescription, --区县时效
          case when ${param.sign_type} = 0 then sum(pres_reach_sum)  else sum(real_reach_sum) end as reach_sum, --达成票数-0为时-1为实际
          case when ${param.sign_type} = 0 then sum(pres_no_reach_sum)  else sum(real_no_reach_sum) end as no_reach_sum, --未达成票量
          case when ${param.sign_type} = 0 then sum(pres_total)  else sum(real_total) end as total,  --总票数
          case when ${param.sign_type} = 0 then CONCAT(LEFT(sum(pres_reach_sum)/sum(pres_total)*100, 4),'%') else CONCAT(LEFT(sum(real_reach_sum)/sum(real_total)*100,4),'%') end as reach_rate --达成率
       FROM
           jms_dws.dws_network_reach_rate
       WHERE
           date_time >= '${param.startTime}'
       AND date_time <= '${param.endTime}' -- 时间
       ${param.senderRegionalId}
       ${param.receiverRegionalId}

       ${param.type}
       ${param.sign_type_sql}
       ${param.island}
       ${param.districtprescription}
       group by date_time,--日期
			    districtprescription --区县时效
       ORDER BY
          date_time,districtprescription ASC -- 时间正序
    `
    const sql2 = `
    select
        sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
        sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
        sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
    from 
      jms_dws.dws_network_reach_rate
    where 
      date_time >= '${param.startTime}'
      AND date_time <= '${param.endTime}' -- 时间
      ${param.senderRegionalId}
      ${param.receiverRegionalId}

      ${param.type}
      ${param.sign_type_sql}
      ${param.island}
      ${param.districtprescription}
      AND districtprescription is not null
    `
    return {sql,sql2}
  },

  // 网点-网点
  AchievingRateNetwork_sql: (params) => {
    /* 
    senderRegionalId:寄件大区
    senderAgentCode:寄件代理区
    senderNetworkCode:寄件网点
    senderProvinceId:寄件省
    senderCityId:寄件市
    senderAreaId:寄件区
    receiverRegionalId:目的大区
    receiverAgentCode:目的代理区
    receiverNetworkCode:目的网点
    receiverProvinceId:目的省
    receiverCityId:目的市
    receiverAreaId:目的区
    island:是否桃花岛
    sign_type:签收类型
    districtprescription:区县时效districtprescription(0-10)
    current:页码
    size:条数 
    startTime:开始时间
    endTime:结束时间
    **/
    const param = disposeparams(params)
    const sql = `
        SELECT
          count(1)
          FROM
              (
                  SELECT
                    date_time
                  FROM
                    jms_dws.dws_network_reach_rate
                  WHERE
                  date_time>='${params.startTime}' and date_time<='${params.endTime}'
                  ${param.type}
                  ${param.sign_type_sql}
                  ${param.island}
                  ${param.districtprescription}
                  ${param.senderRegionalId}
                  ${param.senderAgentCode}
                  ${param.senderNetworkCode}
                  ${param.senderProvinceId}
                  ${param.senderCityId}
                  ${param.senderAreaId}
                  ${param.receiverRegionalId}
                  ${param.receiverAgentCode}
                  ${param.receiverNetworkCode}
                  ${param.receiverProvinceId}
                  ${param.receiverCityId}
                  ${param.receiverAreaId}
              ) t;
              select date_time,--日期
              start_pick_network_name, --寄件网点
              start_regional_desc,  --寄件大区
              start_subordinate_agent_name, --寄件代理区
              start_provider_desc,  --寄件省份
              start_city_desc,  --寄件城市
              start_area_desc, --寄件区县
              sign_network_name,  --签收网点
              sign_regional_desc, --签收大区
              sign_subordinate_agent_name, --签收代理区
              sign_provider_desc, --签收省份
              sign_city_desc,  --签收城市
              sign_area_desc, --签收区县
              districtprescription, --区县时效
              case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end as reach_sum, --达成票数-0为时-1为实际
              case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end as no_reach_sum, --达成票数
              case when ${param.sign_type} = 0 then pres_total  else real_total end as total,  --总票数
              case when ${param.sign_type} = 0 then CONCAT(pres_reach_rate,'%')  else CONCAT(real_reach_rate,'%') end as reach_rate --达成率
        from 
          jms_dws.dws_network_reach_rate
        where 
          date_time>='${param.startTime}' and date_time<='${param.endTime}'
          ${param.type}
          ${param.sign_type_sql}
          ${param.island}
          ${param.districtprescription}
          ${param.senderRegionalId}
          ${param.senderAgentCode}
          ${param.senderNetworkCode}
          ${param.senderProvinceId}
          ${param.senderCityId}
          ${param.senderAreaId}
          ${param.receiverRegionalId}
          ${param.receiverAgentCode}
          ${param.receiverNetworkCode}
          ${param.receiverProvinceId}
          ${param.receiverCityId}
          ${param.receiverAreaId}
        ORDER BY
          date_time,districtprescription ASC -- 时间正序
    `
    const sql2 = `
        select
            sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
            sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
            sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
        from 
          jms_dws.dws_network_reach_rate
        where 
          date_time>='${param.startTime}' and date_time<='${param.endTime}'
          ${param.type}
          ${param.sign_type_sql}
          ${param.island}
          ${param.districtprescription}
          AND districtprescription is not null
          ${param.senderRegionalId}
          ${param.senderAgentCode}
          ${param.senderNetworkCode}
          ${param.senderProvinceId}
          ${param.senderCityId}
          ${param.senderAreaId}
          ${param.receiverRegionalId}
          ${param.receiverAgentCode}
          ${param.receiverNetworkCode}
          ${param.receiverProvinceId}
          ${param.receiverCityId}
          ${param.receiverAreaId}
    `
    return {sql,sql2}
  },

  // 区县到区县
  AchievingRateCounty_sql: (params) => {
    /* 
    senderRegionalId:寄件大区
    senderAgentCode:寄件代理区
    senderNetworkCode:寄件网点
    senderProvinceId:寄件省
    senderCityId:寄件市
    senderAreaId:寄件区
    receiverRegionalId:目的大区
    receiverAgentCode:目的代理区
    receiverNetworkCode:目的网点
    receiverProvinceId:目的省
    receiverCityId:目的市
    receiverAreaId:目的区
    island:是否桃花岛
    sign_type:签收类型
    districtprescription:区县时效districtprescription(0-10)
    current:页码
    size:条数 
    startTime:开始时间
    endTime:结束时间
    **/
    const param = disposeparams(params)
    const sql = `
        SELECT
          count(1)
          FROM
              (
                  SELECT
                    date_time
                  FROM
                    jms_dws.dws_network_reach_rate
                  WHERE
                  date_time>='${params.startTime}' and date_time<='${params.endTime}'
                  ${param.type}
                  ${param.sign_type_sql}
                  ${param.island}
                  ${param.districtprescription}
                  ${param.senderRegionalId}
                  ${param.senderAgentCode}
                  ${param.senderProvinceId}
                  ${param.senderCityId}
                  ${param.senderAreaId}
                  ${param.receiverRegionalId}
                  ${param.receiverAgentCode}
                  ${param.receiverProvinceId}
                  ${param.receiverCityId}
                  ${param.receiverAreaId}
                  group by date_time,--日期
                    start_regional_desc,  --寄件大区
                    start_subordinate_agent_name, --寄件代理区
                    start_provider_desc,  --寄件省份
                    start_city_desc,  --寄件城市
                    start_area_desc, --寄件区县
                    sign_regional_desc, --签收大区
                    sign_subordinate_agent_name, --签收代理区
                    sign_provider_desc, --签收省份
                    sign_city_desc,  --签收城市
                    sign_area_desc, --签收区县
                    districtprescription --区县时效
              ) t;
        select date_time,--日期
          start_regional_desc,  --寄件大区
          start_subordinate_agent_name, --寄件代理区
          start_provider_desc,  --寄件省份
          start_city_desc,  --寄件城市
          start_area_desc, --寄件区县
          sign_regional_desc, --签收大区
          sign_subordinate_agent_name, --签收代理区
          sign_provider_desc, --签收省份
          sign_city_desc,  --签收城市
          sign_area_desc, --签收区县
          districtprescription, --区县时效
          case when ${param.sign_type} = 0 then sum(pres_reach_sum)  else sum(real_reach_sum) end as reach_sum, --达成票数-0为时-1为实际
          case when ${param.sign_type} = 0 then sum(pres_no_reach_sum)  else sum(real_no_reach_sum) end as no_reach_sum, --达成票数
          case when ${param.sign_type} = 0 then sum(pres_total)  else sum(real_total) end as total,  --总票数
          case when ${param.sign_type} = 0 then CONCAT(LEFT(sum(pres_reach_sum)/sum(pres_total)*100, 4),'%') else CONCAT(LEFT(sum(real_reach_sum)/sum(real_total)*100,4),'%') end as reach_rate --达成率
        from 
          jms_dws.dws_network_reach_rate
        where 
          date_time>='${param.startTime}' and date_time<='${param.endTime}'
          ${param.type}
          ${param.sign_type_sql}
          ${param.island}
          ${param.districtprescription}
          ${param.senderRegionalId}
          ${param.senderAgentCode}
          ${param.senderProvinceId}
          ${param.senderCityId}
          ${param.senderAreaId}
          ${param.receiverRegionalId}
          ${param.receiverAgentCode}
          ${param.receiverProvinceId}
          ${param.receiverCityId}
          ${param.receiverAreaId}
        group by date_time,--日期
          start_regional_desc,  --寄件大区
          start_subordinate_agent_name, --寄件代理区
          start_provider_desc,  --寄件省份
          start_city_desc,  --寄件城市
          start_area_desc, --寄件区县
          sign_regional_desc, --签收大区
          sign_subordinate_agent_name, --签收代理区
          sign_provider_desc, --签收省份
          sign_city_desc,  --签收城市
          sign_area_desc, --签收区县
          districtprescription --区县时效
        ORDER BY
          date_time,districtprescription ASC -- 时间正序
    `
    const sql2 = `
    select
        sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
        sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
        sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
    from 
      jms_dws.dws_network_reach_rate
    where 
        date_time>='${param.startTime}' and date_time<='${param.endTime}'
        ${param.type}
        ${param.sign_type_sql}
        ${param.island}
        ${param.districtprescription}
        AND districtprescription is not null
        ${param.senderRegionalId}
        ${param.senderAgentCode}
        ${param.senderProvinceId}
        ${param.senderCityId}
        ${param.senderAreaId}
        ${param.receiverRegionalId}
        ${param.receiverAgentCode}
        ${param.receiverProvinceId}
        ${param.receiverCityId}
        ${param.receiverAreaId}
    `
    return {sql,sql2}
  },

  // 城市到城市
  AchievingRateCity_sql: (params) => {
    /*  
    -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)   寄件省(senderProvinceId)  寄件市(senderCityId)
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode)  目的省(receiverProvinceId)  目的市(receiverCityId)
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    **/
    const param = disposeparams(params)
    const sql = `
          SELECT
            count(1)
            FROM
                (
                    SELECT
                      date_time
                    FROM
                      jms_dws.dws_network_reach_rate
                    WHERE
                    date_time>='${params.startTime}' and date_time<='${params.endTime}'
                    ${param.type}
                    ${param.sign_type_sql}
                    ${param.island}
                    ${param.districtprescription}
                    ${param.senderRegionalId}
                    ${param.senderAgentCode}
                    ${param.senderProvinceId}
                    ${param.senderCityId}
                    ${param.receiverRegionalId}
                    ${param.receiverAgentCode}
                    ${param.receiverProvinceId}
                    ${param.receiverCityId}
                    group by date_time,--日期
                      start_regional_desc,  --寄件大区
                      start_subordinate_agent_name, --寄件代理区
                      start_provider_desc,  --寄件省份
                      start_city_desc,  --寄件城市
                      sign_regional_desc, --签收大区
                      sign_subordinate_agent_name, --签收代理区
                      sign_provider_desc, --签收省份
                      sign_city_desc,  --签收城市
                      districtprescription --区县时效
                ) t;
          select date_time,--日期
            start_regional_desc,  --寄件大区
            start_subordinate_agent_name, --寄件代理区
            start_provider_desc,  --寄件省份
            start_city_desc,  --寄件城市
            sign_regional_desc, --签收大区
            sign_subordinate_agent_name, --签收代理区
            sign_provider_desc, --签收省份
            sign_city_desc,  --签收城市
            districtprescription, --区县时效
            case when ${param.sign_type} = 0 then sum(pres_reach_sum)  else sum(real_reach_sum) end as reach_sum, --达成票数-0为时-1为实际
            case when ${param.sign_type} = 0 then sum(pres_no_reach_sum)  else sum(real_no_reach_sum) end as no_reach_sum, --达成票数
            case when ${param.sign_type} = 0 then sum(pres_total)  else sum(real_total) end as total,  --总票数
            case when ${param.sign_type} = 0 then CONCAT(LEFT(sum(pres_reach_sum)/sum(pres_total)*100, 4),'%') else CONCAT(LEFT(sum(real_reach_sum)/sum(real_total)*100,4),'%') end as reach_rate --达成率
          from 
            jms_dws.dws_network_reach_rate
          where 
            date_time>='${param.startTime}' and date_time<='${param.endTime}'
            ${param.type}
            ${param.sign_type_sql}
            ${param.island}
            ${param.districtprescription}
            ${param.senderRegionalId}
            ${param.senderAgentCode}
            ${param.senderProvinceId}
            ${param.senderCityId}
            ${param.receiverRegionalId}
            ${param.receiverAgentCode}
            ${param.receiverProvinceId}
            ${param.receiverCityId}
          group by date_time,--日期
            start_regional_desc,  --寄件大区
            start_subordinate_agent_name, --寄件代理区
            start_provider_desc,  --寄件省份
            start_city_desc,  --寄件城市
            sign_regional_desc, --签收大区
            sign_subordinate_agent_name, --签收代理区
            sign_provider_desc, --签收省份
            sign_city_desc,  --签收城市
            districtprescription --区县时效
          ORDER BY
            date_time,districtprescription ASC -- 时间正序
    `
    const sql2 = `
    select
        sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
        sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
        sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
    from 
      jms_dws.dws_network_reach_rate
    where 
      date_time>='${param.startTime}' and date_time<='${param.endTime}'
      ${param.type}
      ${param.sign_type_sql}
      ${param.island}
      ${param.districtprescription}
      AND districtprescription is not null
      ${param.senderRegionalId}
      ${param.senderAgentCode}
      ${param.senderProvinceId}
      ${param.senderCityId}
      ${param.receiverRegionalId}
      ${param.receiverAgentCode}
      ${param.receiverProvinceId}
      ${param.receiverCityId}
    `
    return {sql,sql2}
  },

  // 省份到省份
  AchievingRatePovince_sql: (params) => {
    /*  
    -寄件大区(senderRegionalId)  寄件省(senderProvinceId)
    -目的大区(receiverRegionalId)  目的省(receiverProvinceId)
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    **/
    const param = disposeparams(params)
    const sql = `
          SELECT
            count(1)
            FROM
                (
                    SELECT
                      date_time
                    FROM
                      jms_dws.dws_network_reach_rate
                    WHERE
                    date_time>='${params.startTime}' and date_time<='${params.endTime}'
                    ${param.type}
                    ${param.sign_type_sql}
                    ${param.island}
                    ${param.districtprescription}
                    ${param.senderRegionalId}
                    ${param.senderProvinceId}
                    ${param.receiverRegionalId}
                    ${param.receiverProvinceId}
                    group by date_time,--日期
                      start_regional_desc,  --寄件大区
                      start_provider_desc,  --寄件省份
                      sign_regional_desc, --签收大区
                      sign_provider_desc, --签收省份
                      districtprescription --区县时效
                ) t;
          select date_time,--日期
            start_regional_desc,  --寄件大区
            start_provider_desc,  --寄件省份
            sign_regional_desc, --签收大区
            sign_provider_desc, --签收省份
            districtprescription, --区县时效
            case when ${param.sign_type} = 0 then sum(pres_reach_sum)  else sum(real_reach_sum) end as reach_sum, --达成票数-0为时-1为实际
            case when ${param.sign_type} = 0 then sum(pres_no_reach_sum)  else sum(real_no_reach_sum) end as no_reach_sum, --达成票数
            case when ${param.sign_type} = 0 then sum(pres_total)  else sum(real_total) end as total,  --总票数
            case when ${param.sign_type} = 0 then CONCAT(LEFT(sum(pres_reach_sum)/sum(pres_total)*100, 4),'%') else CONCAT(LEFT(sum(real_reach_sum)/sum(real_total)*100,4),'%') end as reach_rate --达成率
          from 
            jms_dws.dws_network_reach_rate
          where 
            date_time>='${param.startTime}' and date_time<='${param.endTime}'
            ${param.type}
            ${param.sign_type_sql}
            ${param.island}
            ${param.districtprescription}
            ${param.senderRegionalId}
            ${param.senderAgentCode}
            ${param.senderProvinceId}
            ${param.receiverRegionalId}
            ${param.receiverAgentCode}
            ${param.receiverProvinceId}
          group by date_time,--日期
            start_regional_desc,  --寄件大区
            start_provider_desc,  --寄件省份
            sign_regional_desc, --签收大区
            sign_provider_desc, --签收省份
            districtprescription --区县时效
          ORDER BY
            date_time,districtprescription ASC -- 时间正序
    `
    const sql2 = `
    select
        sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
        sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
        sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
    from 
      jms_dws.dws_network_reach_rate
    where 
      date_time>='${param.startTime}' and date_time<='${param.endTime}'
      ${param.type}
      ${param.sign_type_sql}
      ${param.island}
      ${param.districtprescription}
      AND districtprescription is not null
      ${param.senderRegionalId}
      ${param.senderAgentCode}
      ${param.senderProvinceId}
      ${param.receiverRegionalId}
      ${param.receiverAgentCode}
      ${param.receiverProvinceId}
    `
    return {sql,sql2}
  },

  // 代理区-代理区
  AchievingRateAgent_sql: (params) => {
    /*  
    -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)   寄件省(senderProvinceId)
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode)  目的省(receiverProvinceId)
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    **/
    const param = disposeparams(params)
    const sql = `
          SELECT
            count(1)
            FROM
                (
                    SELECT
                      date_time
                    FROM
                      jms_dws.dws_network_reach_rate
                    WHERE
                    date_time>='${params.startTime}' and date_time<='${params.endTime}'
                    ${param.type}
                    ${param.sign_type_sql}
                    ${param.island}
                    ${param.districtprescription}
                    ${param.senderRegionalId}
                    ${param.senderAgentCode}
                    ${param.senderProvinceId}
                    ${param.receiverRegionalId}
                    ${param.receiverAgentCode}
                    ${param.receiverProvinceId}
                    group by date_time,--日期
                      start_regional_desc,  --寄件大区
                      start_provider_desc,  --寄件省份
                      start_subordinate_agent_name, --寄件代理区
                      sign_regional_desc, --签收大区
                      sign_provider_desc, --签收省份
                      sign_subordinate_agent_name, --签收代理区
                      districtprescription --区县时效
                ) t;
          select date_time,--日期
            start_regional_desc,  --寄件大区
            start_subordinate_agent_name, --寄件代理区
            start_provider_desc,  --寄件省份
            sign_regional_desc, --签收大区
            sign_subordinate_agent_name, --签收代理区
            sign_provider_desc, --签收省份
            districtprescription, --区县时效
            case when ${param.sign_type} = 0 then sum(pres_reach_sum)  else sum(real_reach_sum) end as reach_sum, --达成票数-0为时-1为实际
            case when ${param.sign_type} = 0 then sum(pres_no_reach_sum)  else sum(real_no_reach_sum) end as no_reach_sum, --达成票数
            case when ${param.sign_type} = 0 then sum(pres_total)  else sum(real_total) end as total,  --总票数
            case when ${param.sign_type} = 0 then CONCAT(LEFT(sum(pres_reach_sum)/sum(pres_total)*100, 4),'%') else CONCAT(LEFT(sum(real_reach_sum)/sum(real_total)*100,4),'%') end as reach_rate --达成率
          from 
            jms_dws.dws_network_reach_rate
          where 
            date_time>='${param.startTime}' and date_time<='${param.endTime}'
            ${param.type}
            ${param.sign_type_sql}
            ${param.island}
            ${param.districtprescription}
            ${param.senderRegionalId}
            ${param.senderAgentCode}
            ${param.senderProvinceId}
            ${param.receiverRegionalId}
            ${param.receiverAgentCode}
            ${param.receiverProvinceId}
          group by date_time,--日期
            start_regional_desc,  --寄件大区
            start_provider_desc,  --寄件省份
            start_subordinate_agent_name, --寄件代理区
            sign_regional_desc, --签收大区
            sign_provider_desc, --签收省份
            sign_subordinate_agent_name, --签收代理区
            districtprescription --区县时效
          ORDER BY
            date_time,districtprescription ASC --时间正序
    `
    const sql2 = `
    select
        sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
        sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
        sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
    from 
      jms_dws.dws_network_reach_rate
    where 
      date_time>='${param.startTime}' and date_time<='${param.endTime}'
      ${param.type}
      ${param.sign_type_sql}
      ${param.island}
      ${param.districtprescription}
      AND districtprescription is not null
      ${param.senderRegionalId}
      ${param.senderAgentCode}
      ${param.senderProvinceId}
      ${param.receiverRegionalId}
      ${param.receiverAgentCode}
      ${param.receiverProvinceId}
    `
    return {sql,sql2}
  },

  // 中心到中心
  AchievingRateCore_sql: (params) => {
    /*  
    -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)   寄件省(senderProvinceId)  寄件转运中心（senderDistributeCode）
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode)  目的省(receiverProvinceId)  目的转运中心（receiverDistributeCode）
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    **/
    const param = disposeparams(params)
    const sql = `
          SELECT
            count(1)
            FROM
                (
                    SELECT
                      date_time
                    FROM
                      jms_dws.dws_network_reach_rate
                    WHERE
                    date_time>='${params.startTime}' and date_time<='${params.endTime}'
                    ${param.type}
                    ${param.sign_type_sql}
                    ${param.island}
                    ${param.districtprescription}
                    ${param.senderRegionalId}
                    ${param.senderAgentCode}
                    ${param.senderDistributeCode}
                    ${param.senderProvinceId}
                    ${param.receiverRegionalId}
                    ${param.receiverAgentCode}
                    ${param.receiverDistributeCode}
                    ${param.receiverProvinceId}
                    group by date_time,--日期
                      start_regional_desc,  --寄件大区
                      start_subordinate_agent_name, --寄件代理区
                      start_provider_desc,  --寄件省份
                      start_center_name,  --始发转运中心
                      sign_regional_desc, --签收大区
                      sign_subordinate_agent_name, --签收代理区
                      sign_provider_desc, --签收省份
                      aim_center_name, --目的转运中心
                      districtprescription --区县时效
                ) t;
          select date_time,--日期
            start_regional_desc,  --寄件大区
            start_subordinate_agent_name, --寄件代理区
            start_provider_desc,  --寄件省份
            start_center_name,  --始发转运中心
            sign_regional_desc, --签收大区
            sign_subordinate_agent_name, --签收代理区
            sign_provider_desc, --签收省份
            aim_center_name, --目的转运中心
            districtprescription, --区县时效
            case when ${param.sign_type} = 0 then sum(pres_reach_sum)  else sum(real_reach_sum) end as reach_sum, --达成票数-0为时-1为实际
            case when ${param.sign_type} = 0 then sum(pres_no_reach_sum)  else sum(real_no_reach_sum) end as no_reach_sum, --达成票数
            case when ${param.sign_type} = 0 then sum(pres_total)  else sum(real_total) end as total,  --总票数
            case when ${param.sign_type} = 0 then CONCAT(LEFT(sum(pres_reach_sum)/sum(pres_total)*100, 4),'%') else CONCAT(LEFT(sum(real_reach_sum)/sum(real_total)*100,4),'%') end as reach_rate --达成率
          from 
            jms_dws.dws_network_reach_rate
          where 
            date_time>='${param.startTime}' and date_time<='${param.endTime}'
            ${param.type}
            ${param.sign_type_sql}
            ${param.island}
            ${param.districtprescription}
            ${param.senderRegionalId}
            ${param.senderAgentCode}
            ${param.senderDistributeCode}
            ${param.senderProvinceId}
            ${param.receiverRegionalId}
            ${param.receiverAgentCode}
            ${param.receiverDistributeCode}
            ${param.receiverProvinceId}
          group by date_time,--日期
            start_regional_desc,  --寄件大区
            start_subordinate_agent_name, --寄件代理区
            start_provider_desc,  --寄件省份
            start_center_name,  --始发转运中心
            sign_regional_desc, --签收大区
            sign_subordinate_agent_name, --签收代理区
            sign_provider_desc, --签收省份
            aim_center_name, --目的转运中心
            districtprescription --区县时效
          ORDER BY
            date_time,districtprescription ASC -- 时间正序
    `
    const sql2 = `
    select
        sum(case when ${param.sign_type} = 0 then pres_reach_sum  else real_reach_sum end) as reach_sum, --达成票数-0为时-1为实际
        sum(case when ${param.sign_type} = 0 then pres_no_reach_sum  else real_no_reach_sum end) as no_reach_sum, --达成票数
        sum(case when ${param.sign_type} = 0 then pres_total  else real_total end) as total  --总票数
    from 
      jms_dws.dws_network_reach_rate
    where 
      date_time>='${param.startTime}' and date_time<='${param.endTime}'
      ${param.type}
      ${param.sign_type_sql}
      ${param.island}
      ${param.districtprescription}
      AND districtprescription is not null
      ${param.senderRegionalId}
      ${param.senderAgentCode}
      ${param.senderDistributeCode}
      ${param.senderProvinceId}
      ${param.receiverRegionalId}
      ${param.receiverAgentCode}
      ${param.receiverDistributeCode}
      ${param.receiverProvinceId}
    `
    return {sql,sql2}
  },

  // 明细查询sql
  AchievingRateDetails_sql: (params) => {
    /*  
     -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)  寄件转运中心（senderDistributeCode） 寄件网点(senderNetworkCode)   寄件省(senderProvinceId)  寄件市(senderCityId)   寄件区(senderAreaId)
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode) 目的转运中心（receiverDistributeCode） 目的网点(receiverNetworkCode)   目的省(receiverProvinceId)  目的市(receiverCityId)   目的区(receiverAreaId)
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    -运单号(waybill_no)  是否达成（is_sign_delay 0否1是 number）  延误类型（delay_type）
    **/
    params.type_data = params.type //判定揽收日期参数
    if (params.type == 2) {  //运行时效 0/时效运行时效  1/实际运行时效
      params.taking_date = `plan_sign_date>='${params.startTime}' and plan_sign_date<='${params.endTime}'`
    } else {
      params.taking_date = `taking_date>='${params.startTime}' and taking_date<='${params.endTime}'`
    }
    params.waybill_no = (params.waybill_no == "" || params.waybill_no == undefined) ? "" : `AND waybill_no =  '${params.waybill_no}' ` //运单号
    if (params.sign_type == 0) {  //运行时效 0/时效运行时效  1/实际运行时效
      params.is_sign_delay = (params.is_sign_delay === "" || params.is_sign_delay == undefined) ? "" : `AND is_pres_standard =  '${params.is_sign_delay}' ` //签收是否达成 0否1是
    } else {
      params.is_sign_delay = (params.is_sign_delay === "" || params.is_sign_delay == undefined) ? "" : `AND is_real_standard =  '${params.is_sign_delay}' ` //签收是否达成0否1是
    }
    params.delay_type = (params.delay_type == "" || params.delay_type == undefined) ? "" : `AND delay_type =  '${params.delay_type}' ` //延误类型
    const param = disposeparams(params)
    // 未配置区县专用
    if (params.Unconfigured) {
      param.districtprescription = `AND districtprescription is null`
    }
    const sql = `
          SELECT
          count(1)
          FROM
              (
                  SELECT
                    taking_date
                  FROM
                    jms_dwd.dwd_waybill_prescription_details
                  WHERE
                  ${param.taking_date}
                  ${param.sign_type_sql}
                  ${param.island}
                  ${param.districtprescription}

                  ${param.senderRegionalId}
                  ${param.senderAgentCode}
                  ${param.senderDistributeCode}
                  ${param.senderNetworkCode}
                  ${param.senderProvinceId}
                  ${param.senderCityId}
                  ${param.senderAreaId}
                  ${param.receiverRegionalId}
                  ${param.receiverAgentCode}
                  ${param.receiverDistributeCode}
                  ${param.receiverNetworkCode}
                  ${param.receiverProvinceId}
                  ${param.receiverCityId}
                  ${param.receiverAreaId}
                  
                  ${param.is_sign_delay}
                  ${param.delay_type}
                  ${params.waybill_no}
              ) t;
        SELECT
          case when ${params.type_data} = 1 then taking_date else  plan_sign_date end as dateTime
          ,waybill_no  -- 运单号
          ,case when island = 0 then '否' else '是' end island -- 是否桃花岛
          ,customer_name  -- 客户名称
          ,start_pick_network_name  -- 始发网点name
          ,start_subordinate_agent_name  -- 始发代理区name
          ,start_provider_desc  -- 始发省份name
          ,start_city_desc  -- 始发城市name
          ,start_area_desc  -- 始发区name
          ,date_format(
              taking_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS taking_time -- 揽收时间
          ,date_format(
              warehous_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS warehous_time -- 入仓时间
          ,date_format(
              send_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS send_time -- 发件扫描时间
          ,start_center_name  -- 始发转运中心name
          ,date_format(
            start_centerarriva_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS start_centerarriva_time -- 始发中心到件扫描时间
          ,arrival_task_no  -- 到车任务号
          ,type_one  -- 类型1
          ,date_format(
              start_centersend_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS start_centersend_time -- 始发中心发件扫描时间
          ,departure_task  -- 始发发车任务单号
          ,aim_center_name  -- 目的转运中心name
          ,date_format(
              aim_centerarriva_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS aim_centerarriva_time -- 目的中心到件扫描时间
          ,type_two  -- 类型2
          ,date_format(
              aim_centersend_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS aim_centersend_time -- 目的中心发件扫描时间
          ,sign_subordinate_agent_name  -- 签收代理区name
          ,sign_provider_desc  -- 签收省份name
          ,sign_city_desc  -- 签收城市name
          ,sign_area_desc  -- 签收区name
          ,deliverytotaltime  -- 派件总耗时
          ,sign_network_name  -- 签收网点name
          ,date_format(
              pres_sign_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS pres_sign_time -- 时效签收时间
          ,date_format(
              real_sign_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS real_sign_time -- 实际签收时间

          ,date_format(
              plan_sign_date,
              '%Y-%m-%d %H:%i:%s'
          ) AS plan_sign_date -- 规划应签收日期
          ,date_format(
              dispatch_sign_time,
              '%Y-%m-%d %H:%i:%s'
          ) AS dispatch_sign_time -- 派件应签收时间
          ,case when is_delivery_delay = 0 then '否' when is_delivery_delay =1 then  '是' else "--" end is_delivery_delay -- 交件是否延误 0否1是
          ,case when is_transport_delay = 0 then '否' when is_transport_delay =1 then  '是' else "--" end is_transport_delay -- 转运是否延误 0否1是  
          ,case when is_sign_delay = 0 then '否' when is_sign_delay =1 then  '是' else "--" end is_sign_delay -- 签收是否延误 0否1是
          ,delay_type  -- 延误类型
          ,districtprescription  -- 区县时效
          ,case when ${param.sign_type} = 0 then pres_prescription else  prescription end as prescription  --运行时效 0/时效运行时效  1/实际运行时效
          ,case when ${param.sign_type} = 0 then (case when is_pres_standard=1 then '是' else '否' end) else  (case when is_real_standard=1 then '是' else '否' end) end as standard  --是否达标 1是0否
        FROM
          jms_dwd.dwd_waybill_prescription_details
        WHERE
          ${param.taking_date}
          ${param.sign_type_sql}
          ${param.island}
          ${param.districtprescription}

          ${param.senderRegionalId}
          ${param.senderAgentCode}
          ${param.senderDistributeCode}
          ${param.senderNetworkCode}
          ${param.senderProvinceId}
          ${param.senderCityId}
          ${param.senderAreaId}
          ${param.receiverRegionalId}
          ${param.receiverAgentCode}
          ${param.receiverDistributeCode}
          ${param.receiverNetworkCode}
          ${param.receiverProvinceId}
          ${param.receiverCityId}
          ${param.receiverAreaId}
          
          ${param.is_sign_delay}
          ${param.delay_type}
          ${params.waybill_no}
        ORDER BY
          taking_date ASC --时间正序
    `
    return sql
  },

  // 区域类型
  AchievingRateAreaType_sql: (params) => {
    /*  
     -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)  寄件省(senderProvinceId) 
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode) 目的省(receiverProvinceId)  
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    展示类型（showTypeValue 必填string  1是汇总  2是明细）
    **/
    params.showTypeValue = params.showTypeValue === '1' ? "" : `districtprescription,` //区县时效
    const param = disposeparams(params)
    const sql = `
    SELECT
    count(1)
    FROM
        (
            SELECT
              date_time
            FROM
              jms_dws.dws_network_reach_rate
            WHERE
              date_time>='${param.startTime}' and date_time<='${param.endTime}'
              ${param.sign_type_sql}
              ${param.island}
              ${param.type}
              ${param.districtprescription}
              ${param.senderRegionalId}
              ${param.senderAgentCode}
              ${param.senderProvinceId}
              ${param.receiverRegionalId}
              ${param.receiverAgentCode}
              ${param.receiverProvinceId}
              group by 
              ${params.showTypeValue}
              date_time--日期
        ) t;
            select date_time, --日期
              ${params.showTypeValue}
              city_reach_sum, --同城达成票数
              city_no_reach_sum,  --同城未达成票数
              city_total,  --同城总票数
              concat(round(city_reach_sum/city_total*100,2),'%') as city_rate,  --同城达成率
              province_reach_sum,  --同省达成票数
              province_no_reach_sum,  --同省不达成票数
              province_total,  --同省总票数
              concat(round(province_reach_sum/province_total*100,2),'%') as province_rate, --同省达成率
              trans_province_reach_sum,  --跨省达成票数
              trans_province_no_reach_sum,  --跨省未达成票数
              trans_province_total,  --跨省总票数
              concat(round(trans_province_reach_sum/trans_province_total*100,2),'%') as trans_province_rate, --跨省达成率
              city_reach_sum+province_reach_sum+trans_province_reach_sum as total_reach_sum,  --合计达成票数
              city_no_reach_sum+province_no_reach_sum+trans_province_no_reach_sum as total_no_reach_sum,  --合计未达成票数
              city_total+province_total+trans_province_total as total,  --合计总票数
              concat(round((city_reach_sum+province_reach_sum+trans_province_reach_sum)/(city_total+province_total+trans_province_total)*100,2),'%') as total_rate --合计达成率          
                from(
                select date_time,--日期
                    ${params.showTypeValue}
                    case when ${param.sign_type} = 0  then sum(case when start_city_id=sign_city_id then pres_reach_sum else 0 end )  else sum(case when start_city_id=sign_city_id then real_reach_sum else 0 end ) end city_reach_sum,
                    case when ${param.sign_type} = 0  then sum(case when start_city_id=sign_city_id then pres_no_reach_sum else 0 end ) else sum(case when start_city_id=sign_city_id then real_no_reach_sum else 0 end )  end city_no_reach_sum,
                    case when ${param.sign_type} = 0  then sum(case when start_city_id=sign_city_id then pres_total else 0 end) else sum(case when start_city_id=sign_city_id then real_total else 0 end)  end city_total,
                    
                    case when ${param.sign_type} = 0  then sum(case when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then pres_reach_sum else 0 end ) else sum(case when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then real_reach_sum else 0 end ) end province_reach_sum,
                    case when ${param.sign_type} = 0  then sum(case when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then pres_no_reach_sum else 0 end ) else sum(case when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then real_no_reach_sum else 0 end ) end province_no_reach_sum,
                    case when ${param.sign_type} = 0  then sum(case when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then pres_total else 0 end ) else sum(case when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then real_total else 0 end ) end province_total,
                    
                    case when ${param.sign_type} = 0  then sum(case when start_provider_id<>sign_provider_id then pres_reach_sum else 0 end )  else sum(case when start_provider_id<>sign_provider_id then real_reach_sum else 0 end ) end trans_province_reach_sum,
                    case when ${param.sign_type} = 0  then sum(case when start_provider_id<>sign_provider_id then pres_no_reach_sum else 0 end ) else sum(case when start_provider_id<>sign_provider_id then real_no_reach_sum else 0 end )  end trans_province_no_reach_sum,
                    case when ${param.sign_type} = 0  then sum(case when start_provider_id<>sign_provider_id then pres_total else 0 end) else sum(case when start_provider_id<>sign_provider_id then real_total else 0 end)  end trans_province_total
            from  
              jms_dws.dws_network_reach_rate
            where 
              date_time>='${param.startTime}' and date_time<='${param.endTime}'
              ${param.sign_type_sql}
              ${param.island}
              ${param.type}
              ${param.districtprescription}
              ${param.senderRegionalId}
              ${param.senderAgentCode}
              ${param.senderProvinceId}
              ${param.receiverRegionalId}
              ${param.receiverAgentCode}
              ${param.receiverProvinceId}
            group by 
            ${params.showTypeValue}
            date_time--日期
                )t
            order by 
            date_time asc
    `
    return sql
  },

  // 区域维度明细
  AchievingRateDailySheetDetails_sql: (params) => {
    /*  
     -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)  寄件省(senderProvinceId) 
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode) 目的省(receiverProvinceId)  
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    **/
    const param = disposeparams(params)
    const sql = `
    SELECT
    count(1)
    FROM
        (
            SELECT
              date_time,
              type,
              districtprescription
            FROM (select 
            case when start_city_id=sign_city_id then '同城'
                       when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then '同省'
                       when start_provider_id<>sign_provider_id then '跨省'  end as type,  --类型（1-同城 2-同省 3-跨省）
                  case when districtprescription>=5 then 5 else districtprescription  end as districtprescription, --静态时效
                  date_time  --日期
              from jms_dws.dws_network_reach_rate 
              WHERE
                date_time>='${param.startTime}' and date_time<='${param.endTime}'
                ${param.sign_type_sql}
                ${param.island}
                ${param.type}
                ${param.districtprescription}
                ${param.senderRegionalId}
                ${param.senderAgentCode}
                ${param.senderProvinceId}
                ${param.receiverRegionalId}
                ${param.receiverAgentCode}
                ${param.receiverProvinceId}) t
              
            
            group by
                date_time,--日期
                type,  --类型
                districtprescription
        ) t;
    select 
        date_time,  --日期
        type,  --类型（1-同城 2-同省 3-跨省）
        districtprescription,  --静态时效
        case when ${param.sign_type} = 0 then sum(pres_total)  
            else sum(real_total) end real_total,  --总票数
        case when ${param.sign_type} = 0 then sum(pres_reach_sum)  
            else sum(real_reach_sum) end reach_sum,  --达成票数
        case when ${param.sign_type} = 0 then concat(round(sum(pres_reach_sum)/sum(pres_total)*100,2),'%')
            else concat(round(sum(real_reach_sum)/sum(real_total)*100,2),'%') end rate  --达成率
        from (
        select 
          case when start_city_id=sign_city_id then '同城' 
            when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then '同省' 
            when start_provider_id<>sign_provider_id then '跨省'  end as type,  --类型（1-同城 2-同省 3-跨省）
            case when districtprescription>=5 then 5 else districtprescription  end as districtprescription, --静态时效
            date_time,  --日期
            start_subordinate_agent_code,  --始发代理区code
            start_subordinate_agent_name,  --始发代理区name
            start_regional_id,  --始发大区id
            start_regional_desc,  --始发大区name
            start_provider_id,  --始发省份id
            start_provider_desc,  --始发省份name
            start_city_id,  --始发城市id
            start_city_desc,  --始发城市name
            sign_subordinate_agent_code,  --签收代理区code
            sign_subordinate_agent_name,  --签收代理区name
            sign_regional_id,  --签收大区id
            sign_regional_desc,  --签收大区name
            sign_provider_id,  --签收省份id
            sign_provider_desc,  --签收省份name
            sign_city_id,  --签收城市id
            sign_city_desc,  --签收城市name
            real_reach_sum, --实际达成票数
            real_no_reach_sum,  --实际未达成票数
            real_total,  --实际总票量
            real_reach_rate,  --实际达成率
            pres_reach_sum,  --时效达成票数
            pres_no_reach_sum,  --时效未达成票数
            pres_total,  --时效总票量
            pres_reach_rate  --时效达成率
        from 
            jms_dws.dws_network_reach_rate
        where 
            date_time>='${param.startTime}' and date_time<='${param.endTime}'
            ${param.sign_type_sql}
            ${param.island}
            ${param.type}
            ${param.districtprescription}
            ${param.senderRegionalId}
            ${param.senderAgentCode}
            ${param.senderProvinceId}
            ${param.receiverRegionalId}
            ${param.receiverAgentCode}
            ${param.receiverProvinceId}
            )t
        group by 
        date_time,--日期
        districtprescription, --区县时效
        type  --寄件大区
        order by date_time,type asc 
    `
    return sql
  },

  // 区域维度汇总
  AchievingRateDailySheet_sql: (params) => {
    /*  
     -寄件大区(senderRegionalId)  寄件代理区(senderAgentCode)  寄件省(senderProvinceId) 
    -目的大区(receiverRegionalId)  目的代理区(receiverAgentCode) 目的省(receiverProvinceId)  
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)  展示类型（showTypeValue 必填  1是汇总  2是明细  全部 0）
    **/
    const param = disposeparams(params)
    const sql = `
    SELECT
    count(1)
    FROM
        (
            SELECT
              date_time
            FROM (select 
            case when start_city_id=sign_city_id then '同城'
                       when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then '同省'
                       when start_provider_id<>sign_provider_id then '跨省'  end as type,  --类型（1-同城 2-同省 3-跨省）
                  case when districtprescription>=5 then 5 else districtprescription  end as districtprescription, --静态时效
                  date_time  --日期
                  from jms_dws.dws_network_reach_rate 
              WHERE
              date_time>='${param.startTime}' and date_time<='${param.endTime}'
                ${param.sign_type_sql}
                ${param.island}
                ${param.type}
                ${param.districtprescription}
                ${param.senderRegionalId}
                ${param.senderAgentCode}
                ${param.senderProvinceId}
                ${param.receiverRegionalId}
                ${param.receiverAgentCode}
                ${param.receiverProvinceId}) t
            group by
                date_time,--日期
                type  --寄件大区
        ) t;
    select 
        date_time,  --日期
        type,  --类型（1-同城 2-同省 3-跨省）
        "汇总" as districtprescription,  --静态时效
        case when ${param.sign_type} = 0 then sum(pres_total)  
            else sum(real_total) end real_total,  --总票数
        case when ${param.sign_type} = 0 then sum(pres_reach_sum)  
            else sum(real_reach_sum) end reach_sum,  --达成票数
        case when ${param.sign_type} = 0 then concat(round(sum(pres_reach_sum)/sum(pres_total)*100,2),'%')
            else concat(round(sum(real_reach_sum)/sum(real_total)*100,2),'%') end rate  --达成率
        from (
        select 
          case when start_city_id=sign_city_id then '同城' 
            when start_provider_id=sign_provider_id and start_city_id<>sign_city_id then '同省' 
            when start_provider_id<>sign_provider_id then '跨省'  end as type,  --类型（1-同城 2-同省 3-跨省）
            case when districtprescription>=5 then 5 else districtprescription  end as districtprescription, --静态时效
            date_time,  --日期
            start_subordinate_agent_code,  --始发代理区code
            start_subordinate_agent_name,  --始发代理区name
            start_regional_id,  --始发大区id
            start_regional_desc,  --始发大区name
            start_provider_id,  --始发省份id
            start_provider_desc,  --始发省份name
            start_city_id,  --始发城市id
            start_city_desc,  --始发城市name
            sign_subordinate_agent_code,  --签收代理区code
            sign_subordinate_agent_name,  --签收代理区name
            sign_regional_id,  --签收大区id
            sign_regional_desc,  --签收大区name
            sign_provider_id,  --签收省份id
            sign_provider_desc,  --签收省份name
            sign_city_id,  --签收城市id
            sign_city_desc,  --签收城市name
            real_reach_sum, --实际达成票数
            real_no_reach_sum,  --实际未达成票数
            real_total,  --实际总票量
            real_reach_rate,  --实际达成率
            pres_reach_sum,  --时效达成票数
            pres_no_reach_sum,  --时效未达成票数
            pres_total,  --时效总票量
            pres_reach_rate  --时效达成率
        from 
            jms_dws.dws_network_reach_rate
        where 
            date_time>='${param.startTime}' and date_time<='${param.endTime}'
            ${param.sign_type_sql}
            ${param.island}
            ${param.type}
            ${param.districtprescription}
            ${param.senderRegionalId}
            ${param.senderAgentCode}
            ${param.senderProvinceId}
            ${param.receiverRegionalId}
            ${param.receiverAgentCode}
            ${param.receiverProvinceId}
            )t
        group by 
        date_time,--日期
        type  --寄件大区
        order by date_time asc 
    `
    return sql
  },


  // 进出港监控
  CompleTransitPort_sql: (params) => {
    /* 
    WdType 查询维度(类型 1大区  2代理区  3转运中心 4 网点 5省份 6市 7区 Number)
    isPurpose 始发或者目的(进出港类型 1 进港  2出港 Number)
    definiteArea  具体区域
    -是否桃花岛(island)  -签收类型(sign_type)  -区县时效(districtprescription)0-10
    -页码(current)  条数(size)  开始时间(startTime)  结束时间(endTime)
    展示类型（showTypeValue 必填string  1是汇总  2是明细）
    **/
    let CFconcretelys = CFdimensionalityType(params)
    params.definiteArea = (params.definiteArea == "" || params.definiteArea == undefined) ? "" : `AND ${CFconcretelys.CFconcretely} =  '${params.definiteArea}'`
    params.showTypeValue = params.showTypeValue === '1' ? "" : `districtprescription,` //区县时效
    const param = disposeparams(params)
    const sql = `
    SELECT
    count(1)
    FROM
        (
            SELECT
              date_time
            FROM
              jms_dws.dws_network_reach_rate
            WHERE
              date_time>='${param.startTime}' and date_time<='${param.endTime}'
              ${param.sign_type_sql}
              ${param.island}
              ${param.type}
              ${param.districtprescription}
              ${param.definiteArea}
             
            group by 
              ${CFconcretelys.CFconcretely},
              ${CFconcretelys.CFconcretelyName},
              ${params.showTypeValue}
              date_time --日期
        ) t;
    select 
      date_time, --日期
      ${params.showTypeValue}
      ${CFconcretelys.CFconcretelyName} as Hierarchieebenenname, --网点 （如果查询条件是出港用始发的字段，如果是进港用签收的字段，根据查询条件类型选择 省份城市区县网点代理区中心改变，对应下方group by）
      case when ${param.sign_type} = 0 then sum(pres_reach_sum)  
            else sum(real_reach_sum) end reach_sum,  --达成票数
      case when ${param.sign_type} = 0 then sum(pres_total)  
           else sum(real_total) end real_total,  --总票数
      case when ${param.sign_type} = 0 then concat(round(sum(pres_reach_sum)/sum(pres_total)*100,2),'%')
           else concat(round(sum(real_reach_sum)/sum(real_total)*100,2),'%') end rate  --达成率
    from jms_dws.dws_network_reach_rate
    where 
      date_time>='${param.startTime}' and date_time<='${param.endTime}'
      ${param.sign_type_sql}
      ${param.island}
      ${param.type}
      ${param.districtprescription}
      ${param.definiteArea}
    group by 
      ${CFconcretelys.CFconcretely}, --网点 （如果查询条件是出港用始发的字段，如果是进港用签收的字段，根据查询条件类型选择 省份城市区县网点代理区中心改变，对应下方group by）
      ${CFconcretelys.CFconcretelyName},
      ${params.showTypeValue}
      date_time --日期
    order by date_time asc
    `
    return sql
  },

  mainLineRoureVerify_sql: (params) => {
    /* 
    请求参数
    -始发大区(senderRegionalId)  -始发代理区(senderAgentCode)  -始发转运中心(senderDistributeCode)
    -目的大区(receiverRegionalId)  -目的代理区(receiverAgentCode)  -目的转运中心(receiverDistributeCode)
    -页码(current)  条数(size)  日期(scantime)【必填】 类型（type）[number类型 1：超七个节点 2：路由迂回 ]
    返回参数
    scantime  -- "日期",
    billcode  -- "运单号",
    scansitecode  -- "责任网点code",
    inputsite  -- "责任网点name",
    subordinate_agent_code  -- "代理区code",
    subordinate_agent_name  -- "代理区name",
    parent_network_code  -- "加盟商code",
    parent_network_name  -- "加盟商name"
    nums  --疑似漏捆绑票数
    **/
    params.senderRegionalId = (params.senderRegionalId == "" || params.senderRegionalId == undefined) ? "" : `AND start_regional_code =  '${params.senderRegionalId}' ` //始发大区
    params.senderAgentCode = (params.senderAgentCode == "" || params.senderAgentCode == undefined) ? "" : `AND start_financial_center_code =  '${params.senderAgentCode}' ` //始发代理区
    params.senderDistributeCode = (params.senderDistributeCode == "" || params.senderDistributeCode == undefined) ? "" : `AND start_center_code =  '${params.senderDistributeCode}' ` //始发转运中心
    params.receiverRegionalId = (params.receiverRegionalId == "" || params.receiverRegionalId == undefined) ? "" : `AND end_regional_code =  '${params.receiverRegionalId}' ` //目的大区
    params.receiverAgentCode = (params.receiverAgentCode == "" || params.receiverAgentCode == undefined) ? "" : `AND end_financial_center_code =  '${params.receiverAgentCode}' ` //目的代理区
    params.receiverDistributeCode = (params.receiverDistributeCode == "" || params.receiverDistributeCode == undefined) ? "" : `AND end_center_code =  '${params.receiverDistributeCode}' ` //目的转运中心
    switch(params.type){
      case 1:
        params.type = "AND num_of_centers>7" //超七个节点
        break;
      case 2:
        params.type = "AND is_circuitous = 1" //路由迂回
        break;
    }
    const sql = `
        SELECT
        count(1)
        FROM
            (
                SELECT
                    update_date
                FROM
                    jms_dws.main_whole_check
                WHERE
                    update_date >= '${params.scantime}'
                    AND update_date <= '${params.scantime}' -- 时间
                    ${params.senderRegionalId}
                    ${params.senderAgentCode}
                    ${params.senderDistributeCode}
                    ${params.receiverRegionalId}
                    ${params.receiverAgentCode}
                    ${params.receiverDistributeCode}
                    ${params.type}
            ) t;
        SELECT
            update_date,  --日期
            start_regional,  --始发大区
            start_financial_center,  --始发代理区
            start_center,  --始发中心
            end_regional,  --目的大区
            end_financial_center,  --目的代理区
            end_center,  --目的中心
            case when num_of_centers>7  then "超七个节点"  else (case when is_circuitous = 1 then '路由迂回' else 'null' end) end as routeType,  --类型
            whole_route,  --路由节点
            case when is_main_route = 1 then main_province 
              else reserve_province end main_province,  --配载网点(省份）
            case when is_main_route = 1 then main_financial 
              else reserve_financial end main_financial,  --配载代理区(代理区）
            case when is_main_route = 1 then main_center
              else reserve_center end main_center,  --配载网点(中心）
            case when is_main_route = 1 then main_name
              else reserve_name end main_name  --配载网点(网点）
        FROM
            jms_dws.main_whole_check
        WHERE
            update_date >= '${params.scantime}'
            AND update_date <= '${params.scantime}' -- 时间
            ${params.senderRegionalId}
            ${params.senderAgentCode}
            ${params.senderDistributeCode}
            ${params.receiverRegionalId}
            ${params.receiverAgentCode}
            ${params.receiverDistributeCode}
            ${params.type}
        ORDER BY
          update_date desc   
        `
    return sql
  },


  // 经营指标-转运及时率明细sql
  transferTimelinessDetailRate_sql: (params) => {
    /*
    请求参数
    scantime:日期
    billcode:运单号
    senderAgentCode:中心所属代理区code
    senderDistributeCode:中心code
    senderNetworkCode:签收网点code
    shipmentNo:到车任务编号
    arrivalTime:到车时间
    branchShipmentNo:发车任务编号
    departureTime:发车时间
    is_timely:是否及时[number]
    返回参数
    send_date:日期
    billcode:运单号
    sign_provider_desc:签收省份
    sign_city_desc:签收城市
    sign_area_desc:签收区县
    sign_network_name:签收网点
    pick_network_name:寄件网点
    pick_network_code:寄件网点编码
    pick_subordinate_agent_name:寄件代理区
    pick_subordinate_agent_code:寄件代理区编码
    first_inputsite:始发转运中心
    first_scansitecode:始发转运中心编码
    pre_inputsite:上一站转运中心
    pre_scansitecode:上一站转运中心编码
    pre_subordinate_agent_name:上一站代理区
    pre_subordinate_agent_code:上一站代理区编码
    sign_subordinate_agent_name:派件代理区
    subordinate_agent_name:中心所属代理区
    inputsite:末端转运中心
    shipment_no:到车任务编号
    arrivalTime_startTime:到车开始时间
    arrivalTime_endTime:到车结束时间
    branch_shipment_no:发车任务编号
    departureTime_startTime:发车开始时间
    departureTime_endTime:发车结束时间
    nextstation:下一站
    cargo_time:应散货时间
    is_timely:转运是否及时 
    */
   
    params.billcode = (params.billcode == "" || params.billcode == undefined) ? "" : `AND billcode =  '${params.billcode}' ` //运单号
    params.senderAgentCode = (params.senderAgentCode == "" || params.senderAgentCode == undefined) ? "" : `AND subordinate_agent_code =  '${params.senderAgentCode}' ` //中心所属代理区code
    params.senderDistributeCode = (params.senderDistributeCode == "" || params.senderDistributeCode == undefined) ? "" : `AND scansitecode =  '${params.senderDistributeCode}' ` //末端转运中心code
    params.senderNetworkCode = (params.senderNetworkCode == "" || params.senderNetworkCode == undefined) ? "" : `AND sign_network_code =  '${params.senderNetworkCode}' ` //签收网点code
    
    params.shipmentNo = (params.shipmentNo == "" || params.shipmentNo == undefined) ? "" : `AND shipment_no =  '${params.shipmentNo}' ` //到车任务编号

    params.arrivalTime_startTime = (params.arrivalTime_startTime == "" || params.arrivalTime_startTime == undefined) ? "" : `AND arrival_time >= '${params.arrivalTime_startTime}'` //到车开始时间
    params.arrivalTime_endTime = (params.arrivalTime_endTime == "" || params.arrivalTime_endTime == undefined) ? "" : `AND arrival_time <= '${params.arrivalTime_endTime}'` //到车结束时间
    params.branchShipmentNo = (params.branchShipmentNo == "" || params.branchShipmentNo == undefined) ? "" : `AND branch_shipment_no =  '${params.branchShipmentNo}' ` //发车任务编号
    params.departureTime_startTime = (params.departureTime_startTime == "" || params.departureTime_startTime == undefined) ? "" : `AND departure_time >= '${params.departureTime_startTime}'` //发车开始时间
    params.departureTime_endTime = (params.departureTime_endTime == "" || params.departureTime_endTime == undefined) ? "" : `AND departure_time <= '${params.departureTime_endTime}'` //发车结束时间
    params.is_timely = (params.is_timely === "" || params.is_timely === undefined) ? "" : `AND is_timely =  ${params.is_timely} ` //是否及时

    const sql = `
        SELECT
        count(1)
        FROM
            (
                SELECT
                    send_date
                FROM
                    jms_dwd.dwd_centersend_timerate_detail
                WHERE
                    send_date >= '${params.scantime}'
                    AND send_date <= '${params.scantime}' -- 时间
                    ${params.billcode}
                    ${params.senderAgentCode}
                    ${params.senderDistributeCode}
                    ${params.senderNetworkCode}
                    ${params.shipmentNo}
                    ${params.arrivalTime_startTime}
                    ${params.arrivalTime_endTime}
                    ${params.branchShipmentNo}
                    ${params.departureTime_startTime}
                    ${params.departureTime_endTime}
                    ${params.is_timely}
            ) t;
        SELECT
            send_date, --日期
            billcode, --运单号
            sign_provider_desc, --签收省份
            sign_city_desc, --签收城市
            sign_area_desc, --签收区县
            sign_network_name, --签收网点
            pick_network_name, --寄件网点
            pick_network_code, --寄件网点编码
            pick_subordinate_agent_name, --寄件代理区
            pick_subordinate_agent_code, --寄件代理区编码
            first_inputsite, --始发转运中心
            first_scansitecode, --始发转运中心编码
            pre_inputsite, --上一站转运中心
            pre_scansitecode, --上一站转运中心编码
            pre_subordinate_agent_name, --上一站代理区
            pre_subordinate_agent_code, --上一站代理区编码
            sign_subordinate_agent_name, --派件代理区
            subordinate_agent_name, --中心所属代理区
            inputsite, --末端转运中心
            shipment_no, --到车任务编号
            date_format(
                arrival_time,
                '%Y-%m-%d %H:%i:%s'
            ) AS arrival_time, -- 到车时间
            date_format(
                departure_time,
                '%Y-%m-%d %H:%i:%s'
            ) AS departure_time, -- 发车时间
            nextstation, --下一站
            date_format(
                cargo_time,
                '%Y-%m-%d %H:%i:%s'
            ) AS cargo_time, -- 应散货时间
            case when is_timely = 1 then "是" else "否" end as is_timely --转运是否及时 
        FROM
            jms_dwd.dwd_centersend_timerate_detail
        WHERE
            send_date >= '${params.scantime}'
            AND send_date <= '${params.scantime}' -- 时间
            ${params.billcode}
            ${params.senderAgentCode}
            ${params.senderDistributeCode}
            ${params.senderNetworkCode}
            ${params.shipmentNo}
            ${params.arrivalTime_startTime}
            ${params.arrivalTime_endTime}
            ${params.branchShipmentNo}
            ${params.departureTime_startTime}
            ${params.departureTime_endTime}
            ${params.is_timely}
        ORDER BY
            send_date desc   
        `
    return sql
  },

  limit_sql: (params) => {
    params.current = params.current == "" ? 1 : params.current - 1 //页码
    params.size = params.size == "" ? 10 : params.size //条数
    const LIMIT = `LIMIT ${params.size} OFFSET ${(params.current) * params.size}`
    return LIMIT
  }
}